package com.aimee.dao;

import com.aimee.entity.QuestionShow;
import com.aimee.entity.Questions;
import com.aimee.entity.User;
import com.aimee.util.Dbutil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.management.Query;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class QuestionsDao {

    /**
     * 创建题目
     *
     * @param question
     * @return
     * @throws SQLException
     */
    public int addQuestion(Questions question) throws SQLException {
        String sql = "INSERT INTO questions (course_id,user_id,question_content,question_img,question_score," +
                "question_type,question_optiona,question_optionb,question_optionc,question_optiond,question_answer,update_time)" +
                "VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        Object parase[] = new Object[12];
        parase[0] = question.getCourse_id();
        parase[1] = question.getUser_id();
        parase[2] = question.getQuestion_content();
        parase[3] = question.getQuestion_img();
        parase[4] = question.getQuestion_score();
        parase[5] = question.getQuestion_type();
        parase[6] = question.getQuestion_optiona();
        parase[7] = question.getQuestion_optionb();
        parase[8] = question.getQuestion_optionc();
        parase[9] = question.getQuestion_optiond();
        parase[10] = question.getQuestion_answer();
        parase[11] = question.getUpdate_time();

        int n = runner.update(conn, sql, parase);
        conn.close();
        return n;
    }

    /**
     * 预览题目
     *
     * @param question_id
     * @return
     * @throws SQLException
     */
    public Questions showQuestion(int question_id) throws SQLException {
        String sql = "SELECT * from questions WHERE question_id=?";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        Questions question = runner.query(conn, sql, new BeanHandler<Questions>(Questions.class), question_id);
        conn.close();
        return question;
    }

    /**
     * 修改题目
     *
     * @param question
     * @return
     * @throws SQLException
     */
    public int updateQuestion(Questions question) throws SQLException {
        String sql = "UPDATE questions SET course_id = ?,question_content =?,question_img=?,question_score=?," +
                "question_optiona=?,question_optionb=?,question_optionc=?,question_optiond=?,question_answer=?,update_time=? " +
                "WHERE question_id =?";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();

        Object[] parse = new Object[11];
        parse[0] = question.getCourse_id();
        parse[1] = question.getQuestion_content();
        parse[2] = question.getQuestion_img();
        parse[3] = question.getQuestion_score();
        parse[4] = question.getQuestion_optiona();
        parse[5] = question.getQuestion_optionb();
        parse[6] = question.getQuestion_optionc();
        parse[7] = question.getQuestion_optiond();
        parse[8] = question.getQuestion_answer();
        parse[9] = question.getUpdate_time();
        parse[10] = question.getQuestion_id();

        int n = runner.update(conn, sql, parse);
        conn.close();
        return n;
    }


    /**
     * 展示题库管理的页面的数据
     * 使用多表查询
     *
     * @return
     * @throws SQLException
     */
    public List show() throws SQLException {
        String sql = "SELECT q.question_id, c.course_name, q.question_type, question_content, u.user_name ,q.update_time FROM \n" +
                "questions q INNER JOIN course c ON q.course_id = c.course_id INNER JOIN `user` u ON q.user_id = u.user_id;";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        List<QuestionShow> list = runner.query(conn, sql, new BeanListHandler<QuestionShow>(QuestionShow.class));
        conn.close();
        return list;
    }

    /**
     * 根据题目 id 删除题库中的题目
     *
     * @param question_id
     * @return
     * @throws SQLException
     */
    public int del(int question_id) throws SQLException {
        String sql = "DELETE from questions WHERE question_id = ?";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();

        int i = runner.update(conn, sql, question_id);
        conn.close();
        return i;
    }

    /**
     * 单个新增题目；
     *
     * @param question
     * @return
     * @throws SQLException
     */
    public int add(String[] question) throws SQLException {
        CourseDao courseDao = new CourseDao();
        int id = courseDao.getCoureseId(question[0]);

        String sql = "INSERT INTO questions (course_id,user_id,question_content,question_type,question_optiona,question_optionb,question_optionc,question_optiond,question_answer,update_time)\n" +
                "VALUES (?,?,?,?,?,?,?,?,?,?);";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();

        int i = runner.update(conn, sql, id, question[1], question[2], question[3], question[4], question[5], question[6], question[7], question[8], question[9]);

        conn.close();
        return i;
    }


}
